package vertxblog.service;

import io.vertx.core.AbstractVerticle;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.eventbus.Message;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.mysqlclient.MySQLConnectOptions;
import io.vertx.mysqlclient.MySQLPool;
import io.vertx.sqlclient.PoolOptions;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.Tuple;
import vertxblog.constants.Key;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据提供Verticle类
 */
public class DataService extends AbstractVerticle {
    private MySQLConnectOptions connectOptions = new MySQLConnectOptions();
    private MySQLPool mySqlClient;


    @Override
    public void start(Promise<Void> startPromise) throws Exception {
        // 注册事件总线
        vertx.eventBus().consumer(Key.GET_ALL_CATEGORY, this::getAllCategory);  // 获取全部分类
        vertx.eventBus().consumer(Key.ADD_CATEGORY, this::addCategory);  // 添加分类
        vertx.eventBus().consumer(Key.GET_CATEGORY_DETAIL, this::getCategoryDetail);  // 添加分类
        vertx.eventBus().consumer(Key.UPDATE_CATEGORY, this::updateCategory);  // 编辑分类
        vertx.eventBus().consumer(Key.DELETE_CATEGORY, this::deleteCategory);  // 删除分类
        vertx.eventBus().consumer(Key.GET_ARTICLE_LIST, this::getArticleList);  // 获取文章列表
        vertx.eventBus().consumer(Key.GET_ARTICLE_DETAIL, this::getArticleDetail);  // 获取文章详情
        vertx.eventBus().consumer(Key.GET_ARTICLE_ARCHIVE, this::getArticleArchive);    // 获取文章归档
        vertx.eventBus().consumer(Key.GET_ARTICLE_ARCHIVE_WITH_CATEID, this::getArticleArchiveWithCateid);    // 获取文章归档
        vertx.eventBus().consumer(Key.ADD_ARTICLE, this::addArticle);    // 添加文章
        vertx.eventBus().consumer(Key.UPDATE_ARTICLE, this::updateArticle);    // 更新文章
        vertx.eventBus().consumer(Key.GET_ARTICLE_IS_EXIST, this::getArticleIsExist);    // 文章是否存在
        vertx.eventBus().consumer(Key.USER_LOGIN, this::userLogin);    // 用户登录
        vertx.eventBus().consumer(Key.DELETE_ARTICLE, this::deleteArt);    // 删除文章


        // 初始化数据库连接
//        connectOptions.setPort(config().getJsonObject("mysql").getInteger("port", 3306))
//            .setHost(config().getJsonObject("mysql").getString("host", "127.0.0.1"))
//            .setDatabase(config().getJsonObject("mysql").getString("database", "vertxblog"))
//            .setUser(config().getJsonObject("mysql").getString("user", "root"))
//            .setPassword(config().getJsonObject("mysql").getString("password", "123456"));

        this.connectOptions.setPort(3306)
            .setHost("127.0.0.1")
            .setDatabase("vertxblog")
            .setUser("root")
            .setPassword("123456");

        this.mySqlClient = MySQLPool.pool(vertx, connectOptions, new PoolOptions().setMaxSize(5));

        Future.<Void>succeededFuture().onComplete(ar -> {
            if (ar.succeeded()) {
                startPromise.complete();
            } else {
                startPromise.fail(ar.cause());
            }
        });
    }


    /**
     * 获取所有分类
     *
     * @param message Message
     */
    private void getAllCategory(Message message) {
        mySqlClient.query("SELECT * FROM category")
            .execute(ar -> {
                if (ar.succeeded()) {
                    var result = ar.result();
                    JsonArray cateList = new JsonArray();
                    result.forEach(item -> {
                        var json = new JsonObject();
                        json.put("id", item.getValue("id"));
                        json.put("name", item.getValue("name"));
                        json.put("state", item.getValue("state"));
                        json.put("createDate", item.getValue("createDate").toString());
                        json.put("lastModifiedDate", item.getValue("lastModifiedDate").toString());
                        cateList.add(json);
                    });
                    message.reply(cateList);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });

    }


    /**
     * 获取一个分类详情
     *
     * @param message Message<HashMap<String, Integer>>
     */
    private void getCategoryDetail(Message<HashMap<String, Integer>> message) {
        int id = message.body().get("id");
        mySqlClient.preparedQuery("SELECT id, name FROM category WHERE id = ? LIMIT 1")
            .execute(Tuple.of(id), ar -> {
                if (ar.succeeded()) {
                    message.reply(ar.result().iterator().next().toJson());
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
    }


    /**
     * 添加分类
     *
     * @param message Message<HashMap<String, String>>
     */
    private void addCategory(Message<HashMap<String, String>> message) {
        String name = message.body().get("name");
        mySqlClient.preparedQuery("INSERT INTO category (name,createDate,lastModifiedDate) VALUES (?, LOCALTIME, LOCALTIME);")
            .execute(Tuple.of(name), ar -> {
                if (ar.succeeded()) {
                    message.reply(true);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
    }


    /**
     * 编辑分类
     *
     * @param message Message<HashMap<String, String>>
     */
    private void updateCategory(Message<HashMap<String, String>> message) {
        int id = Integer.parseInt(message.body().get("id"));
        String name = message.body().get("name");
        mySqlClient.preparedQuery("UPDATE category SET name = ?, lastModifiedDate = LOCALTIME WHERE id = ?")
            .execute(Tuple.of(name, id), ar -> {
                if (ar.succeeded()) {
                    message.reply(true);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
    }


    /**
     * 删除分类
     *
     * @param message Message<HashMap<String, Integer>>
     */
    private void deleteCategory(Message<HashMap<String, Integer>> message) {
        int id = message.body().get("id");
        mySqlClient.preparedQuery("DELETE FROM category WHERE id = ?")
            .execute(Tuple.of(id), ar -> {
                if (ar.succeeded()) {
                    message.reply(true);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
    }


    /**
     * 获取文章列表
     *
     * @param message Message<HashMap<String, String>>
     */
    private void getArticleList(Message<HashMap<String, String>> message) {
        String keywords = message.body().get("keywords");
        int pageSize = Integer.parseInt(message.body().get("pageSize"));
        int currentPage = Integer.parseInt(message.body().get("currentPage"));

        if (keywords == "") {   // 不是搜索文章
            var fTotal = this.getArticleTotal();
            var fArts = this.getArticles(pageSize, currentPage);

            CompositeFuture.all(fTotal, fArts).onComplete(ar -> {
                if (ar.succeeded()) {
                    int total = fTotal.result();
                    int tp = (int) Math.ceil(total / pageSize);
                    int totalPage = (tp == 0 ? 1 : tp);
                    JsonArray list = fArts.result();

                    JsonObject data = new JsonObject();
                    data.put("pagination", new JsonObject()
                        .put("currentPage", currentPage)
                        .put("pageSize", pageSize)
                        .put("totalPage", totalPage)
                        .put("total", total)
                    ).put("list", list);

                    message.reply(data);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });

        }
        // 文章搜索
        else {
            this.searchArticles(keywords, pageSize, currentPage).onComplete(ar -> {
                if (ar.succeeded()) {
                    var resdata = ar.result();
                    int total = resdata.size();
                    int tp = (int) Math.ceil(total / pageSize);
                    int totalPage = (tp == 0 ? 1 : tp);
                    JsonArray list = resdata;

                    JsonObject data = new JsonObject();
                    data.put("pagination", new JsonObject()
                        .put("currentPage", currentPage)
                        .put("pageSize", pageSize)
                        .put("totalPage", totalPage)
                        .put("total", total)
                    ).put("list", list);

                    message.reply(data);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
        }
    }


    /**
     * 获取文章详情
     *
     * @param message Message<HashMap<String, Integer>>
     */
    private void getArticleDetail(Message<HashMap<String, Integer>> message) {
        int id = message.body().get("id");

        mySqlClient.preparedQuery("SELECT * FROM article WHERE id = ? LIMIT 1")
            .execute(Tuple.of(id), ar -> {
                if (ar.succeeded()) {
                    JsonObject article = ar.result().iterator().next().toJson();
                    article.put("tags", new JsonArray());
                    message.reply(article);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });

    }


    /**
     * 文章是否存在
     *
     * @param message Message<HashMap<String, Integer>>
     */
    private void getArticleIsExist(Message<HashMap<String, Integer>> message) {
        int id = message.body().get("id");

        mySqlClient.preparedQuery("SELECT 1 FROM article WHERE id = ? LIMIT 1")
            .execute(Tuple.of(id), ar -> {
                //  && ar.result().iterator().next() != null
                if (ar.succeeded()) {
                    System.out.println(ar.result().iterator().next());
                    System.out.println(ar.result().iterator().next().getValue(1));
                    if (ar.result().iterator().next().getValue(1) == null) {
                        message.reply(true);
                    } else {
                        message.reply(false);
                    }
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });

    }


    /**
     * 获取文章归档
     * @param message   Message
     */
    private void getArticleArchive(Message message) {
        var fCates = this.getCatesWithArtCount();
        var fArts = this.getArticlesGroupByYear();

        CompositeFuture.all(fCates, fArts).onComplete(ar -> {
            if (ar.succeeded()) {
                JsonObject data = new JsonObject();
                var cates = fCates.result();
                var arts = fArts.result();
                data.put("classList", cates);
                data.put("tags", new JsonArray());
                data.put("articleList", arts);

                message.reply(data);
            } else {
                message.fail(500, ar.cause().getMessage());
            }
        });
    }


    /**
     * 获取文章归档,带分类
     * @param message   Message<HashMap<String, Integer>>
     */
    private void getArticleArchiveWithCateid(Message<HashMap<String, Integer>> message) {
        int classId = message.body().get("classId");
        var fCates = this.getCatesWithArtCount();
        var fArts = this.getArticlesGroupByYear(classId);

        CompositeFuture.all(fCates, fArts).onComplete(ar -> {
            if (ar.succeeded()) {
                JsonObject data = new JsonObject();
                var cates = fCates.result();
                var arts = fArts.result();
                data.put("classList", cates);
                data.put("tags", new JsonArray());
                data.put("articleList", arts);

                message.reply(data);
            } else {
                message.fail(500, ar.cause().getMessage());
            }
        });
    }


    /**
     * 添加文章
     *
     * @param message Message<HashMap<String, String>>
     */
    private void addArticle(Message<HashMap<String, String>> message) {
        String title = message.body().get("title");
        int classId = Integer.parseInt(message.body().get("classId"));
        String content = message.body().get("content");
        mySqlClient.preparedQuery("INSERT INTO article (title, classId, content, createDate, lastModifiedDate) VALUES (?, ?, ?, LOCALTIME, LOCALTIME)")
            .execute(Tuple.of(title, classId, content), ar -> {
                if (ar.succeeded()) {
                    message.reply(true);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
    }


    /**
     * 编辑文章
     *
     * @param message Message<HashMap<String, String>>
     */
    private void updateArticle(Message<HashMap<String, String>> message) {
        int id = Integer.parseInt(message.body().get("id"));
        String title = message.body().get("title");
        int classId = Integer.parseInt(message.body().get("classId"));
        String content = message.body().get("content");
        mySqlClient.preparedQuery("UPDATE article SET title = ?,classId = ?,content = ?,lastModifiedDate = LOCALTIME WHERE id = ?")
            .execute(Tuple.of(title, classId, content, id), ar -> {
                if (ar.succeeded()) {
                    message.reply(true);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
    }


    /**
     * 用户登录
     *
     * @param message Message<HashMap<String, String>>
     */
    private void userLogin(Message<HashMap<String, String>> message) {
        String username = message.body().get("username");
        String password = message.body().get("password");

        mySqlClient.preparedQuery("SELECT user.id,user.username,user.nickname FROM user WHERE username=? AND password=? LIMIT 1")
            .execute(Tuple.of(username, password), ar -> {
                if (ar.succeeded()) {
                    var res = ar.result().iterator().next();
                    JsonObject data = new JsonObject();
                    data.put("id", res.getLong("id"));
                    data.put("username", username);
                    data.put("nickname", res.getString("nickname"));

                    message.reply(data);
                } else {
                    message.fail(500, ar.cause().getMessage());
                }
            });
    }


    /**
     * 删除文章
     *
     * @param message Message<HashMap<String, Integer>>
     */
    private void deleteArt(Message<HashMap<String, Integer>> message) {
        int id = message.body().get("id");

        mySqlClient.preparedQuery("DELETE FROM article WHERE id=?").execute(Tuple.of(id), ar -> {
            if (ar.succeeded()) {
                message.reply(true);
            } else {
                message.fail(500, ar.cause().getMessage());
            }
        });
    }





//Future方法==================================================================

    /**
     * Future，获取文章总数
     *
     * @return
     */
    private Future<Integer> getArticleTotal() {
        Promise<Integer> promise = Promise.promise();
        mySqlClient.query("SELECT COUNT(id) FROM article").execute(ar -> {
            if (ar.succeeded()) {
                int total = ar.result().iterator().next().getInteger(0);
                promise.complete(total);
            } else {
                promise.fail(ar.cause());
            }
        });

        return promise.future();
    }

    /**
     * Future，获取文章列表
     *
     * @param pageSize    int
     * @param currentPage int
     * @return
     */
    private Future<JsonArray> getArticles(int pageSize, int currentPage) {
        Promise<JsonArray> promise = Promise.promise();

        int offset = (currentPage - 1) * pageSize;
        JsonArray list = new JsonArray();
        mySqlClient.preparedQuery("SELECT * FROM article ORDER BY createDate DESC LIMIT ?,?")
            .execute(Tuple.of(offset, pageSize), ar -> {
                if (ar.succeeded()) {
                    var rows = ar.result();
                    rows.forEach(item -> {
                        list.add(new JsonObject()
                            .put("tags", new JsonArray())
                            .put("isRecommend", item.getInteger("isRecommend"))
                            .put("isHot", item.getInteger("isHot"))
                            .put("state", item.getInteger("state"))
                            .put("id", item.getInteger("id"))
                            .put("title", item.getString("title"))
                            .put("content", item.getString("content"))
                            .put("classId", item.getInteger("classId"))
                            .put("createDate", item.getValue("createDate").toString())
                            .put("lastModifiedDate", item.getValue("lastModifiedDate").toString())
                            .put("likeCount", item.getInteger("likeCount"))
                            .put("collectCount", item.getInteger("collectCount"))
                            .put("commentCount", item.getInteger("commentCount"))
                            .put("browseCount", item.getInteger("browseCount"))
                        );
                    });

                    promise.complete(list);
                } else {
                    promise.fail(ar.cause());
                }
            });

        return promise.future();
    }

    /**
     * Future，搜索文章
     *
     * @param keywords    String
     * @param pageSize    int
     * @param currentPage int
     * @return
     */
    private Future<JsonArray> searchArticles(String keywords, int pageSize, int currentPage) {
        Promise<JsonArray> promise = Promise.promise();

        int offset = (currentPage - 1) * pageSize;
        JsonArray list = new JsonArray();
        mySqlClient.preparedQuery("SELECT * FROM article WHERE article.title LIKE ? ORDER BY createDate DESC LIMIT ?,?")
            .execute(Tuple.of((keywords+"%"), offset, pageSize), ar -> {
                if (ar.succeeded()) {
                    var rows = ar.result();
                    rows.forEach(item -> {
                        list.add(new JsonObject()
                            .put("tags", new JsonArray())
                            .put("isRecommend", item.getInteger("isRecommend"))
                            .put("isHot", item.getInteger("isHot"))
                            .put("state", item.getInteger("state"))
                            .put("id", item.getInteger("id"))
                            .put("title", item.getString("title"))
                            .put("content", item.getString("content"))
                            .put("classId", item.getInteger("classId"))
                            .put("createDate", item.getValue("createDate").toString())
                            .put("lastModifiedDate", item.getValue("lastModifiedDate").toString())
                            .put("likeCount", item.getInteger("likeCount"))
                            .put("collectCount", item.getInteger("collectCount"))
                            .put("commentCount", item.getInteger("commentCount"))
                            .put("browseCount", item.getInteger("browseCount"))
                        );
                    });

                    promise.complete(list);
                } else {
                    promise.fail(ar.cause());
                }
            });

        return promise.future();
    }

    /**
     * 获取分类列表（带文章数量）
     *
     * @return
     */
    private Future<JsonArray> getCatesWithArtCount() {
        Promise<JsonArray> promise = Promise.promise();

        mySqlClient.query("SELECT id,name,state,createDate,lastModifiedDate,(select count(id) FROM article WHERE category.id = article.classId) AS `count` FROM category")
            .execute(ar -> {
                if (ar.succeeded()) {
                    JsonArray cates = new JsonArray();
                    var rows = ar.result();
                    rows.forEach(item -> {
                        cates.add(item.toJson());
                    });
                    promise.complete(cates);
                } else {
                    promise.fail(ar.cause());
                }
            });

        return promise.future();
    }

    /**
     * 获取文章列表（带年份）
     *
     * @return
     */
    private Future<JsonArray> getArticlesGroupByYear() {
        Promise<JsonArray> promise = Promise.promise();

        mySqlClient.query("SELECT article.*,YEAR(article.createDate) as `year` FROM article GROUP BY `year`,id ORDER BY createDate DESC")
            .execute(ar -> {
                if (ar.succeeded()) {
                    JsonArray arts = new JsonArray();
                    var rows = ar.result();
                    rows.forEach(item -> {
                        arts.add(item.toJson());
                    });
                    promise.complete(arts);
                } else {
                    promise.fail(ar.cause());
                }
            });

        return promise.future();
    }


    /**
     * 获取文章列表（带年份、带分类）
     * @return
     */
    private Future<JsonArray> getArticlesGroupByYear(int classId) {
        Promise<JsonArray> promise = Promise.promise();

        mySqlClient.preparedQuery("SELECT article.*,YEAR(article.createDate) as `year` FROM article WHERE classId = ? GROUP BY `year`,id ORDER BY createDate DESC")
            .execute(Tuple.of(classId),ar -> {
                if (ar.succeeded()) {
                    JsonArray arts = new JsonArray();

                    var rows = ar.result();
                    rows.forEach(item -> {
                        arts.add(item.toJson());
                    });

                    promise.complete(arts);
                } else {
                    promise.fail(ar.cause());
                }
            });

        return promise.future();
    }
//Future方法 end==================================================================


}
